Triggers should work in isolation, with a final conflict detection step

  • Jump to comment-1
    gclazio@hotmail.com2022-07-31T16:39:11+00:00
    Dear pgsql hackers, I am Gianluca Calcagni, a Salesforce Certified Technical Architect. In the course of my career, I accumulated extensive experience working with triggers, both as an architect and as a developer. When I work with multiple developer teams, they often need to create triggers on the same object: when that happens, I usually recommend them to maintain a single common trigger rather than multiple ones - the rationale being that, by sharing the code, they can minimise most "negative" interactions, such as recursion, trigger cascades, for-loops, multiple DMLs on the same record, and so on. In the Salesforce ecosystem, such approach is considered a best practice (for good reason). The real drawback is that such approach is forgoing the natural principle of "separation of concerns"! I have been looking into using trigger frameworks to solve this problem, but there is no trigger framework that is able to meet my main expectations: in short, isolation and conflict detection. This is what I want to see: 1. when an event is firing two or more triggers, then such triggers must be executed within the same transaction, but each trigger must be executed in its own isolated context (in the sense that it cannot see the changes applied by any trigger other than itself) 2. when all the triggers are done, postgres must merge the results together and stage an end-result 3. postgres must raise all possible conflicts (in the sense that, if a specific field on some record has distinct changes applied by different triggers, then the end-result of the transaction is ambiguous hence the entire transaction should fail) 4. If everything goes fine, the end-result of the transaction is finally committed to the database. You may notice that I took inspiration from GIT for most of the concepts above (e.g. "isolation" actually means "forking"). I realize that this is a huge request, but I believe there is some merit in the idea. I also realize that there are very complicated implementation nuances (e.g. in handling foreign keys and constraints), but I am happy to provide more input about my vision of this feature. Looking forward to your feedback! Wish you all a great day, Gianluca
    • Jump to comment-1
      david.g.johnston@gmail.com2022-08-01T14:20:37+00:00
      On Sunday, July 31, 2022, Gianluca Calcagni <gclazio@hotmail.com> wrote: > > The real drawback is that such approach is forgoing the natural principle > of *"separation of concerns"*! I have been looking into using trigger > frameworks to solve this problem, but there is no trigger framework that is > able to meet my main expectations: in short, *isolation* and *conflict > detection*. > > You may notice that I took inspiration from GIT for most of the concepts > above (e.g. "isolation" actually means "forking"). > > I realize that this is a huge request, > So install a single c-language trigger function that does all of that and provide some functions to manage adding delegation commands in user-space. Make it all work with create extension. Users will have to adhere to the guidelines suggested. I am against having core incorporate such code into the main project. The benefits/cost_complexity ratio is too small. IOW, I suspect the only realistic way this gets into core is if you, its champion, pay to have it developed, but even then I don’t think we should accept such a feature even if it was well written. So if you go that route it should leverage extension mechanisms. We may add some code hooks though if those are requested and substantiated. David J.